/*
 * Copyright 2005-2013 finalist.cn. All rights reserved.
 * Support: http://www.finalist.cn
 * License: http://www.finalist.cn/license
 */
package com.finalist.dao.impl;

import java.math.BigDecimal;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import javax.persistence.FlushModeType;
import javax.persistence.NoResultException;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Repository;

import com.finalist.dao.MemberDao;
import com.finalist.entity.Member;
import com.finalist.entity.Order;
import com.finalist.entity.OrderItem;
import com.finalist.entity.Product;
import com.finalist.entity.Order.OrderStatus;
import com.finalist.entity.Order.PaymentStatus;

/**
 * Dao - 会员
 * 
 * @author Finalist IT Group
 * @version 3.0
 */
@Repository("memberDaoImpl")
public class MemberDaoImpl extends BaseDaoImpl<Member, Long> implements MemberDao
{
    private static final Log LOGGER = LogFactory.getLog(MemberDaoImpl.class);

    @Override
    public boolean usernameExists(String username)
    {
        if (username == null)
        {
            return false;
        }
        String jpql = "select count(*) from Member members where lower(members.username) = lower(:username)";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("username", username).getSingleResult();
        return count > 0;
    }

    /**
     * 判断手机号是否存在
     * 
     * @param phone
     *            手机号（必须是数字）
     * @return 手机号是否存在
     */
    @Override
    public boolean phoneExists(String phone)
    {
        if (phone == null)
        {
            return false;
        }
        String jpql = "select count(*) from Member members where members.mobile = :mobile";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("mobile", phone).getSingleResult();
        return count > 0;
    }

    /*
     * (非 Javadoc) <p>Title: openIdExists</p> <p>Description: </p>
     * 
     * @param openId 微信用户id
     * 
     * @return 是否已绑定妙账号并查出用户信息
     * 
     * @see com.finalist.dao.MemberDao#openIdExists(java.lang.String)
     */
    @Override
    public Member openIdExists(String openId)
    {
        if (openId == null)
        {
            return null;
        }
        String jpql = "select members from Member members where members.weiXinOpenId = :openId";
        try
        {
            return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("openId", openId).getSingleResult();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            LOGGER.error(e.toString());
            return null;
        }
    }

    @Override
    public boolean emailExists(String email)
    {
        if (email == null)
        {
            return false;
        }
        String jpql = "select count(*) from Member members where lower(members.email) = lower(:email)";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("email", email).getSingleResult();
        return count > 0;
    }

    @Override
    public Member findByUsername(String username)
    {
        if (username == null)
        {
            return null;
        }
        try
        {
            String jpql = "select members from Member members where lower(members.username) = lower(:username)";
            List<Member> lists = entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("username", username).getResultList();
            if(lists!=null&&lists.size()>0){
            	return lists.get(0);
            }
            return null;
        }
        catch (NoResultException e)
        {
            e.printStackTrace();
            LOGGER.error(e.toString());
            return null;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            LOGGER.error(e.toString());
            return null;
        }
    }

    @Override
    public List<Member> findListByEmail(String email)
    {
        if (email == null)
        {
            return Collections.<Member> emptyList();
        }
        String jpql = "select members from Member members where lower(members.email) = lower(:email)";
        return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("email", email).getResultList();
    }

    @Override
    public Member findByPhone(String phone)
    {
        if (phone == null)
        {
            return null;
        }
        try
        {
            String jpql = "select members from Member members where members.mobile = :mobile";
            return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("mobile", phone).getSingleResult();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            LOGGER.error(e.toString());
            return null;
        }
    }

    @Override
    public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count)
    {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
        Root<Member> member = criteriaQuery.from(Member.class);
        Join<Product, Order> orders = member.join("orders");
        criteriaQuery.multiselect(member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance"), criteriaBuilder.sum(orders.<BigDecimal> get("amountPaid")));
        Predicate restrictions = criteriaBuilder.conjunction();
        if (beginDate != null)
        {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(orders.<Date> get("createDate"), beginDate));
        }
        if (endDate != null)
        {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(orders.<Date> get("createDate"), endDate));
        }
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(orders.get("orderStatus"), OrderStatus.completed), criteriaBuilder.equal(orders.get("paymentStatus"), PaymentStatus.paid));
        criteriaQuery.where(restrictions);
        criteriaQuery.groupBy(member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance"));
        criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal> get("amountPaid"))));
        TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
        if (count != null && count >= 0)
        {
            query.setMaxResults(count);
        }
        return query.getResultList();
    }

    @Override
    public Member findByOpenId(String openId)
    {
        if (openId == null)
        {
            return null;
        }
        try
        {
            String jpql = "select members from Member members where lower(members.openId) = lower(:openId)";
            return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("openId", openId).getSingleResult();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            LOGGER.error(e.toString());
            return null;
        }
    }

    @Override
    public Member findByUsernameAndMobile(String username, String mobile)
    {
        if (null == username || null == mobile)
        {
            return null;
        }
        try
        {
            String jpql = "select members from Member members where members.username = :username and members.mobile=:mobile";
            List<Member> members = entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("username", username).setParameter("mobile", mobile).getResultList();
            if (null != members && members.size() > 0)
            {
                return members.get(0);
            }
            else
            {
                return null;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            LOGGER.error(e.toString());
            return null;
        }

    }

    private static final String[] LOGIN_PROP = { "username", "mobile", "email" };

    @Override
    public Member findLoginMember(List<String> list)
    {

        if (null == list || list.size() == 0)
        {
            return null;
        }
        else
        {

            //            for (String prop : LOGIN_PROP)
            //            {
            Member member;
            for (String value : list)
            {
                member = findByUsername(value);
                if (member != null)
                {
                    return member;
                }
                member = findByPhone(value);
                if (member != null)
                {
                    return member;
                }
                List<Member> memberList = findListByEmail(value);
                if (null != memberList && memberList.size() == 1)
                {
                    return memberList.get(0);
                }
                else if (null != memberList && memberList.size() > 1)
                {
                    LOGGER.error("用户登录数据冲突，请检查！[ email=" + value + ",查询出来的登录用户有多个]");
                    return null;
                }
                //                    StringBuilder jpql = new StringBuilder();
                //                    jpql.append("select members from Member members ");
                //                    jpql.append(" where lower(members.").append(prop).append(") = lower(:").append(prop).append(") ");
                //                    List<Member> members = entityManager.createQuery(jpql.toString(), Member.class).setParameter(prop, value).setFlushMode(FlushModeType.COMMIT).getResultList();
                //                    if (null != members && 1 == members.size())
                //                    {
                //                        return members.get(0);
                //                    }
                //                    else if (null != members && 1 < members.size())
                //                    {
                //                        LOGGER.error("用户登录数据冲突，请检查！[" + prop + "=" + value + ",查询出来的登录用户有多个]");
                //                        return null;
                //                    }
            }
            //            }

            //            List<String> paramList = new ArrayList<String>();
            //            StringBuilder jpql = new StringBuilder();
            //            jpql.append("select members from Member members where 1=2 ");
            //            int i = 0;
            //            for (String prop : LOGIN_PROP)
            //            {
            //                for (String value : list)
            //                {
            //                    jpql.append(" or members.").append(prop).append(" =?").append(++i).append(" ");
            //                    paramList.add(value);
            //                }
            //            }
            //            TypedQuery<Member> query = entityManager.createQuery(jpql.toString(), Member.class).setFlushMode(FlushModeType.COMMIT);
            //            for (int j = 0; j < i; j++)
            //            {
            //                query.setParameter(j + 1, paramList.get(j));
            //            }
            //            List<Member> members = query.getResultList();
            //            if (null != members && 1 == members.size())
            //            {
            //                return members.get(0);
            //            }
            //            else if (null != members && 1 < members.size())
            //            {
            //                LOGGER.error("用户登录数据冲突，请检查！");
            //                return null;
            //            }
            //            else
            //            {
            //                return null;
            //            }

            return null;
        }

    }

	@Override
	public Member findByunionId(String unionId) {
		 if (unionId == null)
	        {
	            return null;
	        }
	        try
	        {
	            String jpql = "select members from Member members where lower(members.unionId) = lower(:unionId)";
	            return entityManager.createQuery(jpql, Member.class).setFlushMode(FlushModeType.COMMIT).setParameter("unionId", unionId).getSingleResult();
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	            LOGGER.error(e.toString());
	            return null;
	        }
	}

	@Override
	public List<Object[]> findByProduct(Product product,int page) {
		try {
			CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
			CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder
					.createQuery(Object[].class);
			Root<Member> member = criteriaQuery.from(Member.class);
			Join<Member, Order> orders = member.join("orders");
			Join<Member, OrderItem> orderItems = orders.join("orderItems");
			Join<Member, Product> _product = orderItems.join("product");
			criteriaQuery.multiselect(
					member.get("id"),
					member.get("username"),
					member.get("gender"),
					member.get("maritalStatus"),
					member.get("industry"),
					member.get("school"),
					member.get("hometown"),
					member.get("hobby"),
					member.get("memo"),
					member.get("headImgUrl")
					);
			Predicate restrictions = criteriaBuilder.conjunction();
			
			restrictions = criteriaBuilder.and(
					restrictions,
					criteriaBuilder.equal(
							_product.get("id"), product.getId()));
			restrictions = criteriaBuilder.and(
					restrictions,
					criteriaBuilder.equal(
							orders.get("isAssociateShow"), true));
			
			criteriaQuery.where(restrictions);
			criteriaQuery.groupBy(member.get("id"));
			criteriaQuery.orderBy(criteriaBuilder.desc(orders.get("goDate")));
			TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery)
					.setFlushMode(FlushModeType.COMMIT);
			query.setFirstResult((page-1)*10);
			query.setMaxResults(10);
			return query.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
